Usenet 1993 July/InfoMagic USENET CD-ROM July 1993.ISO/sources/misc/volume25/shql/part01

Text File | 1991-11-04 | 23.1 KB | 949 lines

Newsgroups: comp.sources.misc,comp.databases
From: [e-mail] (Bruce Momjian)
Subject: v25i015:  shql - Interactively read and execute SQL commands, Part01/01
Message-ID: <[e-mail]>
X-Md4-Signature: dad4edfe43522bb189c5b55cd7a7015c
Date: Tue, 5 Nov 1991 03:07:18 GMT
Approved: [e-mail]

Submitted-by: [e-mail] (Bruce Momjian)
Posting-number: Volume 25, Issue 15
Archive-name: shql/part01
Environment: SQL, UNIX

SHQL is an interactive SQL database engine.  Written as a shell script,
SHQL interprets SQL commands and manipulates flat files based on those
commands.  SHQL is limited in its understanding of SQL constructs.  All
this is outlined in the README file contained in the distribution.  A
demo file is also included to show some examples.

The program has been tested by a group of 25 from comp.databases, and
after several revisions, is ready to go.

Bruce Momjian           | 830 Blythe Avenue               | home: [phone]
[e-mail] | Drexel Hill, Pennsylvania 19026 | work: [phone]
-------------------------------------------------------------------------
#! /bin/sh
# This is a shell archive, meaning:
# 1. Remove everything above the #! /bin/sh line.
# 2. Save the resulting text in a file.
# 3. Execute the file with /bin/sh (not csh) to create:
#    README
#    demo.shql
#    shql
# This archive created: Thu Oct 31 21:06:06 1991
export PATH; PATH=/bin:/usr/bin:$PATH
echo shar: "extracting 'README'" '(2763 characters)'
if test -f 'README'
then
    echo shar: "will not over-write existing file 'README'"
else
cat << \SHAR_EOF > 'README'
                                  S H Q L

    Shql is a program that reads SQL commands interactively and
    executes those commands by creating and manipulating Unix files.

    This program requires a bourne shell that understands functions,
    as well as awk, grep, cut, sort, uniq, join, wc, and sed.

     This script can be invoked with the command

        shql {database name}

      A directory must be created for the database before you may use it.
    This directory will house all data files for a single database.
    All datafiles are created with mode 666 ('rw-rw-rw-'), so create the
    directory with 777 ('rwxrwxrwx') if you want the database to be
    sharable, and 700 ('rwx------') to be private.  Of course, multiple
    databases are possible.  A database called 'mydb' may be created
    as a directory $HOME/shql/mydb, or as $SHQL_ROOT/mydb, where
    $SHQL_ROOT is defined below.

      The program is patterned after Ingres' interactive sql terminal
    monitor program.  Terminal monitor commands begin with a back-slash
    and must be alone on a line.  The \g is the 'go' command, \p is print,
    and \q is quit.  Try 'help commands' for a full list.

    To get started, invoke shql with a database name.  Use the directory
    name you created above. Type

        shql mydb

    if the directory you created was 'mydb'.  Once shql starts up, you
    should see the database name displayed, and then a '*'. At this
    point, the most valuable thing is to type help,

        * help
        * \g

    You may then go on.  Try the demo.

    Shql can execute only one operation at a time, but operations can
    be spread over several lines.

    Shql operations are limited to one table at a time so you can not
    join tables with the usual tablename.fieldname convention,
    but you can create a view of two tables, and use that in a select.

    Subselects are implemented, but must be the last operand of a
    'where' clause, most useful with 'in'.  A limited multi-table
    join can be implemented with 'in' and subselect.

    Use white space liberally, especially around comparison operators,
    equal signs, and parentheses.  In most cases, commas are optional.

    This SQL is type-less, so specify just the column width when creating
    tables.  This is used only for display purposes.  Shql is
    case-sensitive, and expects SQL key words to be in lower case.

    Aggregates (max, min, sum, count, etc.) and NULLs are not
    implemented.

    Commands can be piped into shql, and the table data files are
    tab delimited, so awk scripts can be used to generate reports
    directly from the tables.  Grave accents (`) may be used to
    execute unix command from with shql.  See the demo for an
    example, i.e. "cat demo.shql | shql mydb".

    If you have comments, suggestions, or bug reports contact:

        Bruce Momjian ([e-mail])
SHAR_EOF
fi
echo shar: "extracting 'demo.shql'" '(1991 characters)'
if test -f 'demo.shql'
then
    echo shar: "will not over-write existing file 'demo.shql'"
else
cat << \SHAR_EOF > 'demo.shql'
# Demo for SHQL, version 0.60
# Create table customer
create table customer (
    name 30,
    age  3,
    status 1 )
\p\g

# Put one person in the table
insert into customer values ( 'Fred', 32, 'G' )
\p\g

# Study the table
help customer
\p\g
select * from customer
\p\g

# Add two more people
insert into customer values
( 'Barney', 29, 'G', 'Wilma', 28, 'D' )
\p\g
print customer
\p\g

# Get customers with 'G' status
select * from customer
where status = 'G'
\p\g

# Get sorted list of customers by age
select * from customer
order by age num
\p\g

# Make a table to hold customer status codes and their descriptions
create table codes (
    code 1,
    description 10 )
\p\g

# Insert status codes
insert into codes values
( 'G', 'Good', 'B', 'Bad', 'D', 'Dead Beat' )
\p\g

# Create a view so we can see the customer name and status description
create view custstat ( customer.status = codes.code )
\p\g

# Look at the table
help custstat
\p\g
select * from custstat
\p\g

# Replace 'Barney' with 'Bad Bart'
update customer
set name = 'Bad Bart', status = 'X'
where age = 29
\p\g

print customer
\p\g

# Get all customers that have invalid status'es
select * from customer
where status not in select code
            from codes
\p\g

# Remove 'Fred'
delete customer
where age = 32
\p\g

# Get rid of view
drop view custstat
\p\g

# Create a holding table for old customers
create table oldcust (
    name 30,
    status 1 )
\p\g

# Copy old customer to new table
insert into oldcust (
    name status )
select name status
from customer
where age > 28
\p\g

# Look at table
print oldcust
\p\g

# Delete customers moved over
delete customer
where age > 28
\p\g

print customer
\p\g

# Try a union of the two tables
select name age
from customer
union
select name status
from oldcust
\p\g

# Show example of executing Unix commands
insert into customer
values ( '`date`', `ls / | wc -l`, 'Y' )
\p\g
print customer
\p\g
# Clean up
drop table codes
\p\g
drop table customer
\p\g
drop table oldcust
\p\g
\q
SHAR_EOF
fi
echo shar: "extracting 'shql'" '(16582 characters)'
if test -f 'shql'
then
    echo shar: "will not over-write existing file 'shql'"
else
cat << \SHAR_EOF > 'shql'
#! /bin/sh
#
# shql - version 1.0
#

# DEFINE THESE
SHQL_ROOT="/u/shql"        # system-wide database location
EDITOR="${EDITOR:=/usr/bin/vi}" # default editor if EDITOR not defined
SHELL="${SHELL:=/bin/sh}"     # default editor if EDITOR not defined

# Unix table file postfixes:  @ is attrib, ~ is data, % is view

#set -x           # uncomment for debugging
#set -v
UMASK=`umask`
umask 0000        # share database
trap "echo \"Goodbye\" ; rm -f /tmp/$$ /tmp/$$row" 0 1 2 3 15
set -h            # remember functions

if echo '\c' | grep -s c ; then        # to adapt to System V vs. BSD 'echo'
    NOCR1='-n'            # BSD
    NOCR2=""
else
    NOCR1=""            # System V
    NOCR2='\c'
fi
NL='
'
TAB='    '
export _IFS TABLE CMD NOCR1 NOCR2 NL TAB
_IFS="$IFS"

if [ "$1" = "" ]
then    echo "Missing database name." 1>&2
    echo "The database name must be a directory under $HOME/shql" 1>&2
    echo "    or a directory under $SHQL_ROOT" 1>&2
    exit 1
fi
echo "Database: $1"

if [ -r $HOME/shql/$1 ]
then    cd $HOME/shql/$1
elif [ -r $SHQL_ROOT/$1 ]
then    cd $SHQL_ROOT/$1
else     echo "Unknown database ($1)" 1>&2
    echo "The database name must be a directory under $HOME/shql" 1>&2
    echo "    or a directory under $SHQL_ROOT" 1>&2
    exit 1
fi


#
#**************************************************************************
# syntax
#**************************************************************************
syntax(){
    case "$1" in
        create)    cat <<"END"
CREATE TABLE table_name (
    column_name column_width
    {, ...}
)
or
CREATE VIEW view_name (
    table_or_view1.column1 = table_or_view2.column2
)
END
return 0
;;
        delete) cat <<"END"
DELETE
FROM table_name
{ WHERE where_clause }
END
return 0
;;
        drop) cat <<"END"
DROP TABLE table_name
or
DROP VIEW view_name
END
return 0
;;
        edit) cat <<"END"
EDIT table_name
is a non-standard method of changing a table's field names or display widths.
END
return 0
;;
        help)    cat <<"END"
HELP ALL
or
HELP TABLES
or
HELP VIEWS
or
HELP COMMANDS
or
HELP [CREATE | DELETE | DROP | INSERT | SELECT | UPDATE | WHERE | PRINT | EDIT]
or
HELP table_name
Commands must appear in lower case.
END
return 0
;;
        insert) cat <<"END"
INSERT INTO table_name
    { ( column_name, ... ) }
VALUES ( expression, ...)
or
INSERT INTO table_name
    { ( column_name, ... ) }
subselect
END
return 0
;;
        print) cat <<"END"
PRINT table_name
is a non-standard synonym for SELECT * FROM table_name.
END
return 0
;;
        select) cat <<"END"
SELECT { DISTINCT }
    [ column_name {,...} | * ]
FROM [ table_name | view_name ]
{ WHERE where_clause }
{ ORDER BY column_name { NUM } { ASC | DESC } {, ... }
{ UNION select statement }
'NUM' is a non-standard method for sorting numeric fields.
END
return 0
;;
        update) cat <<"END"
UPDATE table_name
SET column_name = expression {, ... }
{ WHERE where_clause }
END
return 0
;;
        where) cat <<"END"
WHERE [ column_name | value ] [ =, !=, >, <, >=, <=, and, or, not, in ]
      [ column_name | value | subselect ]
Parentheses may be used to group expressions.
END
return 0
;;
    esac
    return 1
}

#
#**************************************************************************
# lookup_field
#**************************************************************************
lookup_field(){
    if [ ! -f $TABLE% ]
    then    RESULT="`grep -n \"^$1    \" $TABLE@`"
    else     RESULT="`grep -n \"^$1    \" $TABLE@ | sed -n 1p`"
    fi
    if [ ! "$RESULT" ]
    then     OUTFIELD="$1"
        return 1
    else    OUTFIELDNUM="`expr "$RESULT" : '\([^:]*\)'`"
        OUTFIELD="\$$OUTFIELDNUM"
        return 0
    fi
}

#
#**************************************************************************
# do_join
#**************************************************************************
do_join(){
    update_view "$1"
    TABLE="$1"
    lookup_field "$2"
    [ "$?" -ne 0 ] && echo "Bad view specifcation ($1.$2)" 1>&2 && return 1
    JFIELD1="$OUTFIELDNUM"
    JFIELD1L1="`expr $JFIELD1 - 1`"
    update_view "$3"
    TABLE="$3"
    lookup_field "$4"
    [ "$?" -ne 0 ] && echo "Bad view specifcation ($3.$4)" 1>&2 && return 1
    JFIELD2="$OUTFIELDNUM"
    JFIELD2L1="`expr $JFIELD2 - 1`"

    ( grep "^$2    " $1@ ;
      grep -v "^$2    " $1@ ;
      grep -v "^$4    " $3@ ) > $5@
    sort -t\     +$JFIELD2L1 $3~ > /tmp/$$
    sort -t\     +$JFIELD1L1 $1~ | \
        join -t\     -j1 $JFIELD1 -j2 $JFIELD2 \
                        - /tmp/$$ > $5~
}

#
#**************************************************************************
# update_view
#**************************************************************************
update_view(){
    [ ! -f "$1%" ] && return 1
    ( do_join `cat $1%` )
}

#
#**************************************************************************
# where
#**************************************************************************
where(){
    shift
    while [ $# -gt 0 -a "$1" != "order" -a "$1" != "union" ]
    do
        [ "$1" = "select" ] &&
            set - X `( SUBSELECT="Y" ;select "$@")` && shift
        case "$1" in
            and)     WHERE="$WHERE && ";;
            or)    WHERE="$WHERE || ";;
            not)    WHERE="$WHERE !" ;;
            =)    WHERE="$WHERE == ";;
            'in')     shift
                set - X `( SUBSELECT='Y';select "$@" )`
                shift
                INWHERE=""
                COMP="=="
                LOGIC="||"
                [ "$LAST" = "not" ] && COMP="=" && LOGIC="&&"
                for VALUE
                do
                    [ "$INWHERE" != "" ] &&
                        INWHERE="$INWHERE $LOGIC"
                    INWHERE="$INWHERE ($WHERE$COMP $VALUE) "
                done
                WHERE="$INWHERE"
                break;;
            *)    lookup_field "$1"
                WHERE="$WHERE $OUTFIELD";;
        esac
        LAST="$1"
        shift
    done
    [ "$WHERE" ] && WHERE=" ( $WHERE ) " && return 0
    echo "Missing 'where' clause" 1>&2
    syntax where
    return 1
}

#
#**************************************************************************
# help
#**************************************************************************
help(){
    if [ ! "$2" ]
    then    echo "Ambiguous syntax, try:" 1>&2 ; syntax help
    elif [ "$2" = "all" ]
    then    ls *@ *% | cut -d@ -f1 | cut -d% -f1 | uniq
    elif [ "$2" = "tables" ]
    then    ls *@ *% | cut -d@ -f1 | cut -d% -f1 | uniq -u
    elif [ "$2" = "views" ]
    then    ls *% | cut -d% -f1
    elif [ "$2" = "commands" ]
    then    cat << "END"
\p is print
\g is go(execute)
\q is quit
\e is edit
\i is include
\w is write
\r is reset(clear)
\s is shell
\p\g print and go
These commands must appear alone on a line.
The number sign(#) may be used at the start of a line for comments.
END
    else    syntax $2 && return
        TABLE="$2"
        update_view "$TABLE"
        if [ -f "$2@" ]
        then    echo "$NL <$2>" && cat "$2@"
            [ -f "${2}%" ] &&echo $NOCR1 "$NL View:    $NOCR2" &&
                set - X `cat $2%` && shift &&
                echo "$1.$2 = $3.$4"
            echo "$NL Rows:    "`cat $TABLE~ | wc -l`
        else     echo "$TABLE does not exist." 1>&2
            syntax help
        fi
    fi
}

#
#**************************************************************************
# create
#**************************************************************************
create(){
    shift
    if [ -f "$2@" -o -f "$2%" ]
    then    echo "Table already exists." 1>&2
    elif [ "$1" = "view" -a $# -gt 2 ]
    then    shift
        if [ $# -ne 6 ]
        then     syntax create
        else
            [ "X$2" != "X(" ] && echo "Bad syntax" 1>&2 &&
                            syntax create && return
            TABLE1="`expr $3 : '\([^\.]*\)'`"
            FIELD1="`expr $3 : '[^\.]*.\(.*\)'`"
            TABLE="$TABLE1"
            lookup_field "$FIELD1"
            [ "$?" -ne 0 ] && echo "Bad table or field name" 1>&2 &&
                                    return
            [ "X$4" != "X=" ] && echo "Bad syntax" 1>&2 &&
                            syntax create && return
            TABLE2="`expr $5 : '\([^\.]*\)'`"
            FIELD2="`expr $5 : '[^\.]*.\(.*\)'`"
            TABLE="$TABLE2"
            lookup_field "$FIELD2"
            [ "$?" -ne 0 ] && echo "Bad table or field name" 1>&2 &&
                                    return
            [ "X$2" != "X(" ] && echo "Bad syntax" 1>&2 &&
                            syntax create && return
            echo "$TABLE1 $FIELD1 $TABLE2 $FIELD2 $1" > $1%
            update_view "$1"
        fi
        echo "OK"
    elif [ "$1" = "table" -a $# -ge 5 ]
    then
        [ "X$3" != "X(" ] && echo "Bad syntax" 1>&2 &&
                            syntax create && return
        TABLE="$2"
        shift 3
        > $TABLE@
        > $TABLE~
        while [ $# -ge 2 ]
        do
            echo "$1    $2" >> $TABLE@
            shift 2
        done
        [ "X$1" != "X)" ] && echo "Bad syntax" 1>&2 &&
                    rm -f $TABLE@ && syntax create && return
        echo "OK"
    else
        echo "Improper syntax ($1)" 1>&2
        syntax create
    fi
    return
}

#
#*************************************************************************
# drop
#**************************************************************************
drop(){
    [ "$2" != "table" -a "$2" != "view" ] &&
        echo "Syntax error." 1>&2 && syntax drop && return
    [ "$2" = "table" -a -f "$3%" ] &&
        echo "Can not drop, $2 is a view, not a table" 1>&2 && return
    [ "$2" = "view" -a ! -f "$3%" ] &&
        echo "Can not drop, $2 is not a view" 1>&2 && return
    if [ -f "$3@" -o -f "$3%" ]
    then    rm -f $3@ $3~ $3%
        echo "OK"
    else      echo "No such table" 1>&2
    fi
}

#
#**************************************************************************
# insert
#**************************************************************************
insert(){
    shift
    [ "$1" != "into" ] && echo "Improper syntax ($1)" 1>&2 &&
        syntax insert && return
    shift
    TABLE="$1"
    update_view "$TABLE" && echo "Can not insert into a view" 1>&2 && return
    [ ! -f "$TABLE@" ] && echo "Table does not exist" 1>&2 && return
    shift
    ATTRIB="`cat $TABLE@ | wc -l`"
    XASGN=""
    XECHO="echo \""
    if [ $# -gt 0 -a "X$1" = "X(" ]
    then    ATTRIB2="0"
        shift
        while [ $# -gt 0 -a "X$1" != "X)" ]
        do
            lookup_field "$1"
            [ "$?" -ne 0 ] && echo "Bad field name. ($1)" 1>&2 &&
                                    return
            XASGN="$XASGN X$OUTFIELDNUM=\`eval echo \$1\` ; shift;"
            shift
            ATTRIB2=`expr $ATTRIB2 + 1`
        done
        [ "X$1" != "X)" ] && echo "Syntax error ($1)" 1>&2 &&
                        syntax insert && return
        shift
        POS="1"
        while [ "$POS" -le "$ATTRIB" ]
        do
            eval X$POS=""
            [ "$POS" != "1" ] && XECHO="$XECHO\$TAB"
            XECHO="$XECHO\$X$POS"
            POS=`expr $POS + 1`
        done
        XECHO="$XECHO\""
        ATTRIB="$ATTRIB2"
    fi
    if [ "$1" = "select" ]
    then     eval set - X "`( SUBSELECT='Y' ; select "$@" )` \)" ; shift
    elif [ "$1" != "values" -o "X$2" != 'X(' ]
        then     echo "Improper syntax ($1)" 1>&2 && syntax insert &&
                                    return
    else    shift 2
    fi
    for LAST do
    : ; done
    [ "X$LAST" != "X)" ] &&
        echo "Improper syntax" 1>&2 && syntax insert && return
    if [ "`expr \( $# - 1 \) % $ATTRIB`" -ne 0 ]
    then     echo "Incorrect number of values." 1>&2
    else    ROWS="`expr \( $# - 1 \) / $ATTRIB`"
        while [ $# -gt 1 ]
        do
            if [ "$XASGN" = "" ]
            then
                echo $NOCR1 "`eval echo $1`$NOCR2" >> $TABLE~
                shift
                while [ "`expr \( $# - 1 \) % $ATTRIB`" -ne 0 ]
                do
                    echo $NOCR1 "$TAB`eval echo $1`$NOCR2"\
                                 >> $TABLE~
                    shift
                done
                echo "" >> $TABLE~
            else    eval $XASGN
                eval $XECHO >> $TABLE~
            fi
        done
        echo "($ROWS rows)"
    fi
}

#
#*************************************************************************
# delete
#**************************************************************************
delete(){
    TABLE="$2"
    update_view "$TABLE" && echo "You can not delete from a view." 1>&2 &&
                                    return
    [ ! -f "$TABLE@" ] && echo "$TABLE does not exit." 1>&2 && return
    WHERE=""
    if [ "$3" = "where" ]
    then     shift 2
        where "$@" &&
        awk -F"    " "! $WHERE { cnt += 1 ; print }
            END { printf \"( %1d rows.)\\n\", (NR - cnt) \
            >\"/tmp/$$row\" }" $TABLE~ > /tmp/$$ &&
            mv /tmp/$$ $TABLE~ && cat /tmp/$$row
    else    echo '('`cat $TABLE~ | wc -l`' rows)'
        > $TABLE~
    fi
}

#
#*************************************************************************
# update
#**************************************************************************
update(){
    TABLE="$2"
    update_view "$TABLE" && echo "Can not update a view." 1>&2 && return
    [ ! -f "$TABLE@" ] && echo "$TABLE does not exit." 1>&2 && return
    [ "$3" != "set" ] && echo "Improper syntax." 1>&2 && syntax update &&
                                    return
    shift 3
    ASSIGN=""
    while [ $# -gt 0 -a "$1" != "where" ]
    do
        lookup_field "$1" && [ "$2" = "=" ] && ASSIGN="$ASSIGN ; "
        ASSIGN="$ASSIGN $OUTFIELD"
        shift
    done
    WHERE=""
    if [ "$1" = "where" ]
    then     where "$@" || return
    fi
    awk -F"    " "BEGIN { OFS = \"    \" }
        $WHERE     { $ASSIGN; cnt += 1 }
            { print }
        END     { printf \"( %1d rows)\\n\", cnt >\"/tmp/$$row\" }" \
        $TABLE~ > /tmp/$$ &&
            mv /tmp/$$ $TABLE~ && cat /tmp/$$row
}

#
#**************************************************************************
# select
#**************************************************************************
select(){
    UNION="Y"
    while [ "$UNION" != "" ]
    do
        FROM=""
        UNION=""
        for TABLE
        do
            [ "$FROM" ] && break
            [ "$TABLE" = "from" ] && FROM="Y"
        done
        [ ! "$FROM" ] && echo "Syntax error." 1>&2 && syntax select &&
                                    return
        [ ! -f "$TABLE@" ] && echo "$TABLE does not exist." 1>&2 &&
                                    return
        update_view "$TABLE"
         shift
        DISTINCT=""
        [ "$1" = "distinct" ] && DISTINCT="Y" && shift
        FIELDS=""
        PRINTF=""
        while [ "$1" != "from" ]
        do
            if [ "$1" = '*' ]
            then    shift
                set - X `cat $TABLE@ | cut -d\     -f1` "$@"
                shift
            else    lookup_field "$1"
                [ "$?" -ne 0 ] && \
                    echo "Bad field name ($1)" 1>&2 &&
                                    return
                [ "$FIELDS" ] && FIELDS="$FIELDS,"
                FIELDS="$FIELDS $OUTFIELD"
                if [ "$SUBSELECT" = "" ]
                then     [ ! "$PRINTF" ] && PRINTF="|"
                    WIDTH=`expr "$RESULT" : \
                        '[^    ]*    \(.*\)'`
                    PRINTF="$PRINTF%-$WIDTH.${WIDTH}s|"
                else    [ "$PRINTF" ] &&
                    PRINTF="$PRINTF    "
                    PRINTF="$PRINTF\\\"%s\\\""
                fi
                shift
            fi
        done
        shift 2
        WHERE=""
        SORT=""
        while [ $# -ne 0 ]
        do
            if [ "$1" = "where" ]
            then
                where "$@" &&
                WHERE="$WHERE || NR == 1"
                shift
            elif [ "$1" = "order" ]
            then     [ "$2" != "by" ] &&
                    echo "Syntax error ($2)" 1>&2 &&
                    syntax select && return
                shift 2
                while [ $# -gt 0 -a "$1" != "union" ]
                do
                    if [     "$1" != "asc" -a \
                        "$1" != "desc" -a \
                        "$1" != "num" ]
                    then    lookup_field "$1"
                        [ "$?" -ne 0 ] &&
                echo "Bad field name ($1)" 1>&2 && return
                        [ "$SORT" = "" ] &&
                            SORT="sort -t\"    \" "
                        SORTL="`expr $OUTFIELDNUM - 1`"
                        SORT="$SORT +$SORTL"
                        [ "$2" = "num" ] &&
                            SORT="${SORT}n"
                        [ "$2" = "desc" ] &&
                            SORT="${SORT}r"
                        [ "$3" = "desc" ] &&
                            SORT="${SORT}r"
                        SORT="$SORT -$OUTFIELDNUM"
                    fi
                    shift
                done
            elif [ "$1" = "union" ]
            then    shift
                UNION="Y"
                break
            else    shift
            fi
        done

        if [ "$DISTINCT" != "" ]
        then    if [ "$SORT" = "" ]
            then    DIST="sort | uniq | tee /tmp/$$row"
            else    DIST="uniq | tee /tmp/$$row"
            fi
        else    DIST="cat"
        fi

        TABLEFILE="$TABLE~"
        [ "$SORT" != "" ] && cat $TABLE~ | eval "$SORT" > /tmp/$$ &&
                            TABLEFILE="/tmp/$$"

        if [ "$SUBSELECT" != "" ]
        then    awk -F"    " "$WHERE {printf \"$PRINTF \", $FIELDS }" \
                            $TABLEFILE |eval "$DIST"
        else     ( set - X `cut -d\     -f1 $TABLE@` ; shift
              echo $NOCR1 "-$1-$NOCR2" ; shift
                for HEADING
              do
                echo $NOCR1 "$TAB-$HEADING-$NOCR2"
               done ; echo "" ) |
            awk -F"    " \
            "$WHERE { cnt += 1 ; printf \"$PRINTF\\n\", $FIELDS }
            END    { printf \"( %1d rows)\\n\", (cnt - 1) \
            >\"/tmp/$$row\" }" - $TABLEFILE | eval "$DIST" \
                && if [ "$DISTINCT" = "" ]
                then    cat /tmp/$$row
                else     X=`expr \`cat /tmp/$$row|wc -l\` - 1`
                    echo '('$X' rows)'
                fi
        fi
    done
}

#
#**************************************************************************
# main
#**************************************************************************
while :
do
    while :
    do
        echo $NOCR1 "* $NOCR2"
        read LINE || exit
        case "$LINE" in
            p)  echo "$_CMD";;
            g)  break;;
            pg) echo "$_CMD" ; break ;;
            r)  echo "reset" ; _CMD="";;
            s)  umask $UMASK ; $SHELL ; umask 0000;;
            e)  umask $UMASK ; echo "$_CMD" > /tmp/$$
                $EDITOR /tmp/$$; _CMD="`cat /tmp/$$`"
                umask 0000;;
            i)  echo $NOCR1 "Enter include file: $NOCR2"
                read LINE
                [ -f "$LINE" ] && _CMD="$_CMD`cat $LINE`$NL" &&
                echo "$LINE included";;
            w)  echo $NOCR1 "Enter output file: $NOCR2"
                read LINE
                [ "$LINE" ] && umask $UMASK &&
                echo "$_CMD" > "$LINE" && umask 0000 &&
                echo "$LINE written";;
            q)  exit 0;;
            \#*) [ "$NEW" = "Y" ] && _CMD="" ;;
            *)  [ "$NEW" = "Y" ] && _CMD=""
                _CMD="$_CMD$LINE$NL";;
        esac
        NEW=""
    done

    CMD=`echo "$_CMD" | sed -e "s/\'/\"/g" \
        -e 's/\"\([^\"]*\)\"/\"\\\"\1\\\"\"/g' \
        -e 's/</\\\</g' -e 's/>/\\\>/g' -e 's/\*/\\\*/g' \
        -e 's/(/\\\(/g' -e 's/)/\\\)/g'`
    [ ! "$CMD" ] && continue
    IFS="$_IFS,"
    eval set - X $CMD
    shift
    IFS="$_IFS"
    NEW="Y"
    case $1 in
        select)     select "$@";;
        create)     create "$@";;
        delete)     delete "$@";;
        drop)         drop "$@";;
        insert)     insert "$@";;
        update)     update "$@";;
        edit)        [ "$2" ] && $EDITOR $2@;;
        help)        help "$@";;
        print)        select "select" '*' "from" "$2";;
        *)         echo "Missing or unrecognized command." 1>&2 ;;
    esac
done

SHAR_EOF
chmod +x 'shql'
fi
exit 0
#    End of shell archive
exit 0 # Just in case...
--
Kent Landfield                   INTERNET: [e-mail]
Sterling Software, IMD           UUCP:     uunet!sparky!kent
Phone:    [phone]         FAX:      [phone]
Please send comp.sources.misc-related mail to [e-mail].